package com.sunda.spmsoversea.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.sunda.spmsoversea.dto.OverseaRequisitionQueryDTO;
import com.sunda.spmsoversea.entity.OverseaRequisition;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 领用申请表 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-09-22
 */
@Mapper
public interface OverseaRequisitionMapper extends BaseMapper<OverseaRequisition> {

    @Select("<script>" +
            "SELECT ORQ.AUTO_ID_REQUISITION AS \"autoIdRequisition\",\n" +
            "       ORQ.UUID_REQUISITION AS \"uuidRequisition\",\n" +
            "       ORQ.SPMS_ID AS \"spmsId\",\n" +
            "       ORQ.CREATE_DATE AS \"createDate\",\n" +
            "       ORQ.APPROVAL_DATE AS \"approvalDate\",\n" +
            "       ORQ.BUSINESS_TYPE AS \"businessType\",\n" +
            "       ORQ.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       ORQ.CREATE_USERID AS \"createUserid\",\n" +
            "       SU.LAST_NAME AS \"createUsername\",\n" +
            "       ORQ.WERKS AS \"werks\",\n" +
            "       ORQ.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "       ORQ.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       ORQ.RELATE_DOCUMENT AS \"relateDocument\",\n" +
            "       ORQ.COST_CENTER AS \"costCenter\",\n" +
            "       ORQ.COST_CENTER_NAME AS \"costCenterName\",\n" +
            "       ORQ.DATA_VERSION AS \"dataVersion\",\n" +
            "       ORQ.REMARKS AS \"remarks\",\n" +
            "       ORQ.COMMENTS AS \"comments\",\n" +
            "       ORQ.CREATE_TIME AS \"createTime\",\n" +
            "       ORQ.UPDATE_TIME AS \"updateTime\"\n" +
            "FROM OVERSEA_REQUISITION ORQ " +
            "   LEFT JOIN SPMS_USER SU ON ORQ.CREATE_USERID = SU.WORK_CODE\n" +
            "WHERE ORQ.SPMS_STATUS != '0' " +
            " <when test='dto.uuidRequisition!=null and dto.uuidRequisition != \"\"'> AND ORQ.UUID_REQUISITION = #{dto.uuidRequisition}</when>" +
            " <when test='dto.spmsId!=null and dto.spmsId != \"\"'> AND ORQ.SPMS_ID = #{dto.spmsId}</when>" +
            " <when test='dto.spmsStatus!=null and dto.spmsStatus != \"\"'> AND ORQ.SPMS_STATUS = #{dto.spmsStatus}</when>" +
            " <when test='dto.werks!=null and dto.werks != \"\"'> AND ORQ.WERKS = #{dto.werks}</when>" +
            " <when test='dto.workshopCode!=null and dto.workshopCode != \"\"'> AND ORQ.WORKSHOP_CODE = #{dto.workshopCode}</when>" +
            " <when test='dto.whsLocationCode!=null and dto.whsLocationCode != \"\"'> AND ORQ.WHS_LOCATION_CODE = #{dto.whsLocationCode}</when>" +
            " <when test='dto.beginDate!=null and dto.beginDate != \"\"'> AND ORQ.CREATE_DATE &gt;= #{dto.beginDate}</when>" +
            " <when test='dto.endDate!=null and dto.endDate != \"\"'> AND ORQ.CREATE_DATE &lt;= #{dto.endDate}</when>" +
            " <when test='dto.createUser!=null and dto.createUser != \"\"'> " +
            "   AND INSTR(ORQ.CREATE_USERID || ',' || SU.LAST_NAME, #{dto.createUser}, 1, 1) > 0 " +
            " </when>" +
            " ORDER BY ORQ.SPMS_STATUS ASC, ORQ.UPDATE_TIME DESC " +
            "</script>")
    List<Map<String, Object>> getOverseaRequisitionIPage(IPage page, @Param("dto")OverseaRequisitionQueryDTO dto);

    @Select("SELECT ORQ.AUTO_ID_REQUISITION AS \"autoIdRequisition\",\n" +
            "       ORQ.UUID_REQUISITION AS \"uuidRequisition\",\n" +
            "       ORQ.SPMS_ID AS \"spmsId\",\n" +
            "       ORQ.CREATE_DATE AS \"createDate\",\n" +
            "       ORQ.APPROVAL_DATE AS \"approvalDate\",\n" +
            "       ORQ.BUSINESS_TYPE AS \"businessType\",\n" +
            "       ORQ.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       ORQ.CREATE_USERID AS \"createUserid\",\n" +
            "       SU.LAST_NAME AS \"createUsername\",\n" +
            "       ORQ.WERKS AS \"werks\",\n" +
            "       ORQ.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "       ORQ.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       ORQ.RELATE_DOCUMENT AS \"relateDocument\",\n" +
            "       ORQ.COST_CENTER AS \"costCenter\",\n" +
            "       ORQ.COST_CENTER_NAME AS \"costCenterName\",\n" +
            "       ORQ.DATA_VERSION AS \"dataVersion\",\n" +
            "       ORQ.REMARKS AS \"remarks\",\n" +
            "       ORQ.COMMENTS AS \"comments\",\n" +
            "       ORQ.CREATE_TIME AS \"createTime\",\n" +
            "       ORQ.UPDATE_TIME AS \"updateTime\"\n" +
            "FROM OVERSEA_REQUISITION ORQ " +
            "   LEFT JOIN SPMS_USER SU ON ORQ.CREATE_USERID = SU.WORK_CODE\n" +
            "WHERE ORQ.SPMS_STATUS != '0' " +
            "   AND ORQ.UUID_REQUISITION = #{uuidRequisition}")
    Map<String, Object> getOverseaRequisition(@Param("uuidRequisition") String uuidRequisition);

    @Select("SELECT ROWNUM AS \"rowId\",\n" +
            "       RE.UUID_REQUISITION AS \"uuidRequisition\",\n" +
            "       R.AUTO_ID_REQUISITION AS \"autoIdRequisition\",\n" +
            "       R.SPMS_ID AS \"spmsId\",\n" +
            "       R.CREATE_DATE AS \"createDate\",\n" +
            "       R.BUSINESS_TYPE AS \"businessType\",\n" +
            "       R.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       R.CREATE_USERID AS \"createUserid\",\n" +
            "       SU.LAST_NAME AS \"createUsername\",\n" +
            "       R.WERKS AS \"werks\",\n" +
            "       R.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "       R.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       R.COST_CENTER AS \"costCenter\",\n" +
            "       R.DATA_VERSION AS \"dataVersion\",\n" +
            "       R.REMARKS AS \"remarks\",\n" +
            "       R.COMMENTS AS \"comments\",\n" +
            "       R.APPROVAL_DATE AS \"approvalDate\"\n" +
            "FROM (\n" +
            "    SELECT UUID_REQUISITION, APPROVAL_DATE\n" +
            "    FROM OVERSEA_REQUISITION ORQ\n" +
            "    WHERE ORQ.SPMS_STATUS = '3'\n" +
            "      AND WERKS = #{werks}\n" +
            "      AND\n" +
            "          TO_NUMBER(TO_DATE(#{currentDate}, 'yyyy-MM-dd') -\n" +
            "                    TO_DATE(ORQ.APPROVAL_DATE, 'yyyy-MM-dd')) >= 3\n" +
            "    UNION\n" +
            "    SELECT UUID_REQUISITION, APPROVAL_DATE\n" +
            "    FROM OVERSEA_REQUISITION ORQ\n" +
            "    WHERE ORQ.SPMS_STATUS = '5'\n" +
            "      AND\n" +
            "          TO_NUMBER(TO_DATE(#{currentDate}, 'yyyy-MM-dd') -\n" +
            "                    TO_DATE(ORQ.APPROVAL_DATE, 'yyyy-MM-dd')) >= 3\n" +
            "      AND ORQ.SPMS_ID IN (\n" +
            "          SELECT OWO.RELATE_DOCUMENT FROM OVERSEA_WHS_OUT OWO\n" +
            "          WHERE OWO.SPMS_STATUS = '1'\n" +
            "            AND OWO.WERKS = #{werks}\n" +
            "          ) ORDER BY APPROVAL_DATE ASC\n" +
            "    ) RE\n" +
            "    LEFT JOIN OVERSEA_REQUISITION R ON RE.UUID_REQUISITION = R.UUID_REQUISITION\n" +
            "    LEFT JOIN SPMS_USER SU ON R.CREATE_USERID = SU.WORK_CODE ")
    /**
     领用申请单状态为 3(审批通过) 审批通过日期已超过 3 天；
     领用申请单状态为 5(已生成出库任务) 审批通过日期已超过 3 天；无有效出库任务 或 有状态为 1 进行中的出库任务； */
    List<Map<String, Object>> getRequisitionOverdue3(@Param("werks") String werks, @Param("currentDate") String currentDate);

}
